package com.dingding.dao.sql;

/**
 * @ClassName: UserOrderDaoSql
 * @Description: 订单通用SQL
 * @author yhq
 * @date 2015-10-26 上午11:37:53
 */
public class UserOrderDaoSql {

	/**
	 * 查看用户最近订单SQL
	 * 
	 * @author yhq
	 */
	public static final String GET_LAST_USER_ORDER = "SELECT ud.loginId,ud.nickName,ud.userAvatar,ud.tellphone,u.setOutTime,u.totalMoney,u.redBag,u.message,u.number,u.startAddress,u.startLongitude,u.startLatitude,u.endAddress,u.endLongitude,u.endLatitude,u.orderStatus,u.oid,u.orderType,u.placeOrderUserId,u.grabOrderUserId,CASE WHEN u.orderStatus = 2 THEN u.grabOrderTime ELSE u.createTime END orderValidateTime,ud.userTabloidAvatar,vi.vehicleType,vi.vehicleModel,vi.vehicleColor,vi.vehicleImage,vi.lisencePlate,u.distance,ud.sex,u.isLongDistance,u.ucid,u.couponMoney,ud.userLevel FROM userordert u INNER JOIN userdetail ud ON ud.loginId = CASE WHEN u.placeOrderUserId = ? AND u.grabOrderUserId IS NOT NULL THEN u.grabOrderUserId ELSE u.placeOrderUserId END INNER JOIN vehicleinfo vi ON vi.loginId = ud.loginId WHERE (u.placeOrderUserId = ? OR u.grabOrderUserId = ?) AND u.orderStatus IN (1,2,3) ORDER BY u.createTime;";

	/**
	 * 车主抢单查看对方用户信息SQL
	 * 
	 * @author cheshaowei
	 */

	public static final String DRIVER_GRABORDER_GET_USER_INFO = "SELECT u.aliasId,ud.userAvatar,ud.nickName,ud.tellphone,u.loginEquipment,ul.tellphone AS sidePhoneNo,ud.userTabloidAvatar,ud.sex,vi.vehicleType,vi.vehicleModel,vi.vehicleColor,vi.vehicleImage,vi.lisencePlate,ud.nickName AS 'driverName',ud.userLevel  FROM userlogin u INNER JOIN userdetail ud  ON ud.loginId = ? INNER JOIN userdetail ul ON ul.loginId = u.uid  LEFT JOIN vehicleinfo vi ON vi.loginId = ud.loginId  WHERE u.uid = ?";

	/**
	 * 乘客抢单查看对方用户信息SQL
	 * 
	 * @author cheshaowei
	 */
	public static final String PASSAGER_GRABORDER_GET_USER_INFO = "SELECT u.aliasId,ul.userAvatar,ul.nickName,ul.tellphone,u.loginEquipment,ud.tellphone AS sidePhoneNo,ul.userTabloidAvatar,ul.sex,vi.vehicleType,vi.vehicleModel,vi.vehicleColor,vi.vehicleImage,vi.lisencePlate,ud.nickName AS 'driverName',ul.userLevel FROM userlogin u INNER JOIN userdetail ul ON ul.loginId = ? INNER JOIN userdetail ud ON ud.loginId = u.uid INNER JOIN vehicleinfo vi ON vi.loginId = ud.loginId WHERE u.uid = ?";

	/**
	 * 乘客抢单后响应给乘客的车主的信息
	 */
	public static final String PASSAGER_GRABORDER_GES_USER_INFO = "SELECT ud.nickName, ud.userAvatar, vi.lisencePlate, vi.vehicleImage, vi.vehicleColor, vi.vehicleType, vi.vehicleModel, ud.userTabloidAvatar FROM userdetail ud INNER JOIN vehicleinfo vi ON vi.loginId = ud.loginId WHERE ud.loginId = ?";
	/**
	 * 我的订单——已完成
	 */
	public static final String GET_USER_ORDER_COMPLETED = "SELECT CASE WHEN (u.orderType = 1 AND u.placeOrderUserId = ?) OR (u.orderType = 2 AND u.grabOrderUserId = ?) THEN 1 ELSE 2 END orderType, u.setOutTime, u.createTime, u.totalMoney, u.startAddress, u.endAddress, u.cancelType, ud.UserAvatar, ud.NickName, u.oid, ud.userTabloidAvatar, u.isLongDistance, u.couponMoney, u.cancelNumber,ud.userLevel,u.distance, u.driverRcvIntegral, u.message, u.number, u.isComment,u.isCarShare FROM userorder u LEFT JOIN userdetail ud ON ud.loginId = CASE WHEN u.grabOrderUserId = ? THEN u.placeOrderUserId ELSE u.grabOrderUserId END WHERE u.orderStatus = ? AND (u.placeOrderUserId = ? OR u.grabOrderUserId = ?) ORDER BY u.endTime DESC LIMIT ?,?";
	/**
	 * 我的订单——已取消
	 */
	public static final String GET_USER_ORDER_CANCELED = "SELECT CASE WHEN (u.orderType = 1 AND u.placeOrderUserId = ?) OR (u.orderType = 2 AND u.grabOrderUserId = ?) THEN 1 ELSE 2 END orderType,u.setOutTime,u.createTime,u.totalMoney,u.startAddress,u.endAddress,u.cancelType,ud.UserAvatar,ud.NickName, u.oid,ud.userTabloidAvatar,u.isLongDistance,u.couponMoney,u.cancelNumber,ud.userLevel, u.distance, u.driverRcvIntegral, u.message, u.number, u.isComment,u.isCarShare,u.cancelUserId FROM userorder u LEFT JOIN userdetail ud ON ud.loginId = CASE WHEN u.placeOrderUserId = ? AND u.grabOrderUserId IS NOT NULL THEN u.grabOrderUserId ELSE u.placeOrderUserId END WHERE u.orderStatus = ? AND (u.placeOrderUserId = ? OR u.grabOrderUserId = ?) ORDER BY u.cancelTime DESC LIMIT ?,?";
	/**
	 * 推送SQL(乘客支付、乘客确认到达和取消订单)
	 */
	public static final String PASSENGER_PAY_PUSH = "SELECT u.aliasId,u.loginEquipment,ud.tellphone,udp.nickName,ud.nickName AS 'driverName',v.lisencePlate FROM userlogin u INNER JOIN userdetail ud ON u.uid = ud.loginId INNER JOIN userdetail udp ON udp.loginId = ? LEFT JOIN vehicleinfo v ON u.uid = v.loginId WHERE u.uid = ?";

	/**
	 * 给乘客退款
	 */
	public static final String PASSENGER_REFUND = "UPDATE useraccount u SET u.currencyAmount = u.currencyAmount + ? WHERE u.loginId = ?";

	/**
	 * 获取附近列表(无筛选条件)
	 * 1:智能排序  2:最早出发 3：离我最近   4：女士优先   5：男士优先
	 */
	public static final String getNearByUserListNotParam(Integer sortCondition) {

		StringBuilder sBuilder = new StringBuilder();

		sBuilder.append(
				"SELECT ut.placeOrderUserId, ut.oid, ud.nickName, ud.userAvatar, ut.setOutTime, ut.startAddress, ut.startLongitude, ut.startLatitude, ut.endAddress, ut.endLongitude, ut.endLatitude, ut.totalMoney, ut.payDistance, ut.marker, ut.orderStatus, ut.redBag, ut.message, ut.number, ud.tellphone, ud.userTabloidAvatar, ud.totalWeldingProcessNumber, ud.totalTakeNumber, ud.sex, vi.lisencePlate, vi.vehicleModel, vi.vehicleType, vi.vehicleColor, ut.distance,ut.payCity,ut.isLongDistance,ud.userLevel FROM userordert ut INNER JOIN userdetail ud ON ut.placeOrderUserId = ud.loginId INNER JOIN vehicleinfo vi ON ut.placeOrderUserId = vi.loginId WHERE ut.orderType = ? AND ut.orderStatus = 1 AND ut.placeOrderUserId != ?");

		if (sortCondition == 4) { // 女士优先
			sBuilder.append(" order by ud.sex desc");
		} else if (sortCondition == 5) { // 男士优先
			sBuilder.append(" order by ud.sex asc");
		} else if (sortCondition == 1) { // 智能排序
			sBuilder.append(" order by ut.setOutTime asc");
		} else if (sortCondition == 2) { // 最早出发
			sBuilder.append(" order by ut.setOutTime asc");
		}

		sBuilder.append(" LIMIT ?,?");

		return sBuilder.toString();
	}

	/**
	 * 获取附近列表(有筛选条件)
	 */

	// public static final String GET_NEARBY_USER_LIST_PARAM = "SELECT
	// ut.placeOrderUserId,ut.oid,ud.nickName,ud.userAvatar,ut.setOutTime,ut.startAddress,ut.startLongitude,ut.startLatitude,ut.endAddress,ut.endLongitude,ut.endLatitude,ut.totalMoney,ut.payDistance,ut.marker,ut.orderStatus,ut.redBag,ut.message,ut.number,ud.tellphone,ud.userTabloidAvatar,ud.totalWeldingProcessNumber,ud.totalTakeNumber,ut
	// payDistance FROM userordert ut INNER JOIN userdetail ud ON
	// ut.placeOrderUserId = ud.loginId WHERE ut.orderType = ? AND
	// ut.orderStatus = 1 AND ROUND(6378.138*2*ASIN(SQRT(POW(SIN(( ? *PI()/180-
	// ut.startLatitude *PI()/180)/2),2)+COS( ? *PI()/180)*COS( ut.startLatitude
	// * PI()/180)*POW(SIN( ( ? *PI()/180- ut.startLongitude *PI()/180)/2),2))))
	// <= ? AND ut.setOutTime BETWEEN ? AND ? AND
	// ROUND(6378.138*2*ASIN(SQRT(POW(SIN(( ? *PI()/180- ut.endLatitude
	// *PI()/180)/2),2)+COS( ? *PI()/180)*COS( ut.endLatitude *
	// PI()/180)*POW(SIN( ( ? *PI()/180- ut.endLongitude *PI()/180)/2),2)))) <=
	// ? AND ut.payCity = ? AND ut.placeOrderUserId != ? LIMIT ?,?";

	// public static final String GET_NEARBY_USER_LIST_PARAM = "SELECT
	// ut.placeOrderUserId,ut.oid,ud.nickName,ud.userAvatar,ut.setOutTime,ut.startAddress,ut.startLongitude,ut.startLatitude,ut.endAddress,ut.endLongitude,ut.endLatitude,ut.totalMoney,ut.payDistance,ut.marker,ut.orderStatus,ut.redBag,ut.message,ut.number,ud.tellphone,ud.userTabloidAvatar,ud.totalWeldingProcessNumber,ud.totalTakeNumber,ud.sex,vi.lisencePlate,vi.vehicleModel,vi.vehicleType,vi.vehicleColor
	// FROM userordert ut INNER JOIN userdetail ud ON ut.placeOrderUserId =
	// ud.loginId INNER JOIN vehicleinfo vi ON ut.placeOrderUserId = vi.loginId
	// WHERE ut.orderType = ? AND ut.orderStatus = 1 AND
	// ROUND(6378.138*2*ASIN(SQRT(POW(SIN(( ? *PI()/180- ut.startLatitude
	// *PI()/180)/2),2)+COS( ? *PI()/180)*COS( ut.startLatitude *
	// PI()/180)*POW(SIN( ( ? *PI()/180- ut.startLongitude *PI()/180)/2),2))))
	// <= ? AND ut.setOutTime BETWEEN ? AND ? AND
	// ROUND(6378.138*2*ASIN(SQRT(POW(SIN(( ? *PI()/180- ut.endLatitude
	// *PI()/180)/2),2)+COS( ? *PI()/180)*COS( ut.endLatitude *
	// PI()/180)*POW(SIN( ( ? *PI()/180- ut.endLongitude *PI()/180)/2),2)))) <=
	// ? AND ut.payCity = ? AND ut.placeOrderUserId != ? LIMIT ?,?";

	public static final String GET_NEARBY_USER_LIST_PARAM = "SELECT ut.placeOrderUserId,ut.oid,ud.nickName,ud.userAvatar,ut.setOutTime,ut.startAddress,ut.startLongitude,ut.startLatitude,ut.endAddress,ut.endLongitude,ut.endLatitude,ut.totalMoney,ut.payDistance,ut.marker,ut.orderStatus,ut.redBag,ut.message,ut.number,ud.tellphone,ud.userTabloidAvatar,ud.totalWeldingProcessNumber,ud.totalTakeNumber,ud.sex,vi.lisencePlate,vi.vehicleModel,vi.vehicleType,vi.vehicleColor,ut.distance FROM userordert ut INNER JOIN userdetail ud ON ut.placeOrderUserId = ud.loginId INNER JOIN vehicleinfo vi ON ut.placeOrderUserId = vi.loginId WHERE ut.orderType = ? AND ut.orderStatus = 1 AND ROUND(6378.138*2*ASIN(SQRT(POW(SIN(( ? *PI()/180- ut.startLatitude *PI()/180)/2),2)+COS( ? *PI()/180)*COS( ut.startLatitude * PI()/180)*POW(SIN( ( ? *PI()/180- ut.startLongitude *PI()/180)/2),2)))) <= ? AND ut.setOutTime BETWEEN ? AND ? AND ROUND(6378.138*2*ASIN(SQRT(POW(SIN(( ? *PI()/180- ut.endLatitude *PI()/180)/2),2)+COS( ? *PI()/180)*COS( ut.endLatitude * PI()/180)*POW(SIN( ( ? *PI()/180- ut.endLongitude *PI()/180)/2),2)))) <= ? AND ut.payCity = ?  AND ut.placeOrderUserId != ? LIMIT ?,?";

	/**
	 * 自动取消(获取带取消订单列表)
	 */
	public static final String GET_USER_LIST_BY_CANCEL = "SELECT u.oid,u.orderStatus,u.orderType,passengerul.uid AS 'passengerId',passengerul.loginEquipment AS 'passengerEquipment',passengerudl.nickName AS 'passengerName',passengerudl.tellphone     AS 'passengerPhone',driverul.uid               AS 'driverId',driverul.loginEquipment    AS 'driverEquipment',driverudl.nickName         AS 'driverName',driverudl.tellphone        AS 'driverPhone',u.totalMoney,passengerul.aliasId        AS 'passengerAliasId',driverul.aliasId           AS 'driverAliasId',u.distance,u.alipayMoney,u.payMoney,u.couponMoney,u.createTime,u.setOutTime,u.startAddress,u.endAddress,u.isLongDistance,u.ucId,v.lisencePlate FROM userordert u LEFT JOIN userlogin passengerul ON (u.placeOrderUserId = passengerul.uid AND u.orderType = 1) OR (u.grabOrderUserId =  passengerul.uid AND u.orderType = 2) LEFT JOIN userdetail passengerudl ON passengerudl.loginId = passengerul.uid LEFT JOIN userlogin driverul ON (u.placeOrderUserId = driverul.uid AND u.orderType = 2) OR (u.grabOrderUserId =  driverul.uid AND u.orderType = 1) LEFT JOIN userdetail driverudl ON driverul.uid = driverudl.loginId LEFT JOIN vehicleinfo v ON v.loginId = driverudl.loginId WHERE (TIMESTAMPDIFF(SECOND,DATE_ADD(u.grabOrderTime,INTERVAL 10 DAY_MINUTE),NOW()) >= 0 AND u.orderStatus = 2) OR (TIMESTAMPDIFF(SECOND,DATE_SUB(u.setOutTime,INTERVAL 5 DAY_MINUTE),NOW()) >= 0 AND u.orderStatus = 1)";

	/**
	 * 自动取消(批量处理待取消订单)
	 */
	public static final String BATCH_HANDLE = "INSERT INTO userorder (oid, placeOrderUserId, payCity, setOutTime, startAddress, startLongitude, startLatitude, endAddress, endLongitude, endLatitude, distance, payMoney, alipayMoney, totalMoney, number, redBag, message, payDistance, marker, grabOrderUserId, grabOrderTime, orderStatus, createTime, payTime, cancelTime, endTime, orderType, cancelType, cancelNumber, cancelDesc, isLongDistance, couponMoney, ucId, payType, isComment, psgRcvIntegral, driverRcvIntegral) SELECT u.oid, u.placeOrderUserId, u.payCity, u.setOutTime, u.startAddress, u.startLongitude, u.startLatitude, u.endAddress, u.endLongitude, u.endLatitude, u.distance, u.payMoney, u.alipayMoney, u.totalMoney, u.number, u.redBag, u.message, u.payDistance, u.marker, u.grabOrderUserId, u.grabOrderTime, 5 AS orderStatus, u.createTime, u.payTime, u.cancelTime, u.endTime, u.orderType, 2 AS cancelType, u.cancelNumber, u.cancelDesc, u.isLongDistance, u.couponMoney, u.ucId, u.payType, 1,0,0 FROM userordert u WHERE (TIMESTAMPDIFF(SECOND,DATE_ADD(u.grabOrderTime,INTERVAL 10 DAY_MINUTE),NOW()) >= 0 AND u.orderStatus = 2) OR (TIMESTAMPDIFF(SECOND,DATE_SUB(u.setOutTime,INTERVAL 5 DAY_MINUTE),NOW()) >= 0 AND u.orderStatus = 1)";

	/**
	 * 自动取消(批量删除)
	 */
	public static final String BATCH_DELETE = "DELETE FROM userordert WHERE (TIMESTAMPDIFF(SECOND,DATE_ADD(grabOrderTime,INTERVAL 10 DAY_MINUTE),NOW()) >= 0 AND orderStatus = 2) OR (TIMESTAMPDIFF(SECOND,DATE_SUB(setOutTime,INTERVAL 5 DAY_MINUTE),NOW()) >= 0 AND orderStatus = 1)";

	/**
	 * 自动确认到达(获取待确认到达订单列表)
	 */
	public static final String AUTO_CONFIRM_ARRIVAL_LIST = "SELECT u.oid,u.orderStatus,u.orderType,passengerul.uid AS 'passengerId',passengerul.loginEquipment AS 'passengerEquipment',passengerudl.nickName AS 'passengerName',passengerudl.tellphone     AS 'passengerPhone',driverul.uid               AS 'driverId',driverul.loginEquipment    AS 'driverEquipment',driverudl.nickName         AS 'driverName',driverudl.tellphone        AS 'driverPhone',u.totalMoney,passengerul.aliasId        AS 'passengerAliasId',driverul.aliasId           AS 'driverAliasId',u.distance,u.alipayMoney,u.payMoney,u.couponMoney FROM userordert u LEFT JOIN userlogin passengerul ON (u.placeOrderUserId = passengerul.uid AND u.orderType = 1) OR (u.grabOrderUserId = passengerul.uid AND u.orderType = 2) LEFT JOIN userdetail passengerudl ON passengerudl.loginId = passengerul.uid LEFT JOIN userlogin driverul ON (u.placeOrderUserId = driverul.uid AND u.orderType = 2) OR (u.grabOrderUserId = driverul.uid AND u.orderType = 1) LEFT JOIN userdetail driverudl ON driverul.uid = driverudl.loginId WHERE TIMESTAMPDIFF(SECOND,DATE_ADD(u.setOutTime,INTERVAL 2 DAY_HOUR),NOW()) >= 0 AND u.orderStatus = 3";

	/**
	 * 自动确认到达(批量处理待取消订单)
	 */
	public static final String BATCH_HANDLE_CONFIRM_ARRIVAL = "INSERT INTO userorder (oid, placeOrderUserId, payCity, setOutTime, startAddress, startLongitude, startLatitude, endAddress, endLongitude, endLatitude, distance, payMoney, alipayMoney, totalMoney, number, redBag, message, payDistance, marker, grabOrderUserId, grabOrderTime, orderStatus, createTime, payTime, cancelTime, endTime, orderType, cancelType, cancelNumber, cancelDesc, isLongDistance, couponMoney, ucId, payType,isComment,psgRcvIntegral,driverRcvIntegral) SELECT u.oid, u.placeOrderUserId, u.payCity, u.setOutTime, u.startAddress, u.startLongitude, u.startLatitude, u.endAddress, u.endLongitude, u.endLatitude, u.distance, u.payMoney, u.alipayMoney, u.totalMoney, u.number, u.redBag, u.message, u.payDistance, u.marker, u.grabOrderUserId, u.grabOrderTime, 4 AS orderStatus, u.createTime, u.payTime, u.cancelTime, u.endTime, u.orderType, cancelType, u.cancelNumber, u.cancelDesc, u.isLongDistance, u.couponMoney, u.ucId, u.payType, 1,0,0 FROM userordert u WHERE TIMESTAMPDIFF(SECOND,DATE_ADD(u.payTime,INTERVAL 2 DAY_HOUR),NOW()) >= 0 AND u.orderStatus = 3";

	/**
	 * 自动确认到达(批量删除)
	 */
	public static final String BATCH_DELETE_CONFIRM_ARRIVAL = "DELETE FROM userordert WHERE TIMESTAMPDIFF(SECOND,DATE_ADD(payTime,INTERVAL 2 DAY_HOUR),NOW()) >= 0 AND orderStatus = 3";

	/**
	 * 根据用户Id获取未完成订单列表
	 */
	public static final String EXIST_NOT_COMPLETE_ORDER = "SELECT u.oid,u.placeOrderUserId,u.setOutTime,u.distance,u.isLongDistance FROM userordert u WHERE u.placeOrderUserId = ? OR u.grabOrderUserId = ?";

	/**
	 * 根据用户Id获取用户启用的路线
	 */
	public static final String GET_ALL_ENABLE_USERROUTE = "SELECT u.urId,u.setOutTime,u.startLongitude,u.startLatitude,u.purposeLongitude,u.purposeLatitude,u.routeType FROM Userroute u WHERE u.isEnable = 1 AND u.loginId = ?";

	/**
	 * 用户下单-获取要推送的目标用户信息
	 */
	public static final String GET_PUSH_USER_INFO(Integer userType) {

		StringBuilder sBuilder = new StringBuilder();

		sBuilder.append(
				"SELECT ur.setOutTime,ur.startLongitude,ur.startLatitude,ur.purposeLongitude,ur.purposeLatitude,ul.aliasId,ul.loginEquipment,ups.orderReminder,ups.weekendPush,ups.regularRouteConditions FROM userpushsetting ups INNER JOIN userroute ur ON ups.loginId = ur.loginId INNER JOIN userlogin ul ON ups.loginId = ul.uid WHERE (ups.orderReminder = 1 OR ups.weekendPush = 1) AND ur.isEnable = 1 AND ul.userStatus = 1");

		if (userType == 1) {
			sBuilder.append(" AND ul.userRole in (1,2)");
		} else if (userType == 2) {
			sBuilder.append(" AND ul.userRole in (1)");
		}

		sBuilder.append(" AND ups.loginId != ?");

		return sBuilder.toString();
	}

	/**
	 * 根据订单Id获取订单信息
	 */
	public static final String GET_ORDER_INFO_BY_ID = "SELECT u.oid,ud.loginId,ud.nickName,ud.userAvatar,ud.userTabloidAvatar,u.setOutTime,u.startAddress,u.startLongitude,u.startLatitude,u.endAddress,u.endLongitude,u.endLatitude,u.totalMoney,u.orderStatus,u.orderType,u.redBag,u.message,u.number,ud.tellphone,ud.sex,v.lisencePlate,v.vehicleModel,v.vehicleType,v.vehicleColor,u.distance,u.isLongDistance,u.isCarShare,u.setOutCondition,u.parOrderId,ud.userLevel FROM userordert u INNER JOIN userdetail ud ON u.placeOrderUserId = ud.loginId LEFT JOIN vehicleinfo v ON ud.loginId = v.loginId WHERE u.oid = ?";

	/**
	 * 设置订单状态为已评价
	 * 
	 * @author CHEHUANBO
	 * 
	 */
	public static final String SET_ORDER_ALREADY_EVALUATE = "UPDATE userorder uo set uo.isComment = 2 where uo.oid = ?";

	/**
	 * 
	 * 订单完成后，如果用户参加活动有可能赠送积分。
	 * 如果赠送积分需要将获赠积分数更新到订单表中，当前sql由此产生
	 * @author CHEHUANBO
	 * 
	 */
	public static final String UPDATE_CURRENTORDER_RECEIVE_INTEGRAL = "UPDATE userorder uo set uo.driverRcvIntegral = ? where uo.oid = ?";

	/**
	 * 获取车主ID
	 * 
	 * 乘客评价完车主后，需要对车主重新计算一个平均信誉值。
	 * 需要用到车主ID,当前SQL由此产生
	 * 
	 * @author CHEHUANBO
	 */
	public static final String GET_DRIVERID = "SELECT CASE WHEN uo.orderType = 2 THEN uo.grabOrderUserId ELSE uo.placeOrderUserId END driverId FROM userorder uo WHERE uo.orderStatus = 4 AND uo.oid = ?";

}
